/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.Students;
import com.tces.model.SubjectSchedules;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author arnolda
 */
public class SubjectScheduleDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(SubjectScheduleDAO.class);

    /**
     * This will retrieve all the Student's subject open for evaluation.
     *
     * @param studentID
     * @return list of Student's subject
     * @throws Exception
     */
    public static List<SubjectSchedules> getSubjectsForEvaluation(int studentID) throws Exception {
        List<SubjectSchedules> subjectList = new ArrayList<SubjectSchedules>();
        String sql = "SELECT\n"
                + "so.subject_offering_id,\n"
                + "so.subject_offering_name,\n"
                + "ss.subject_schedule_id,\n"
                + "ss.subject_schedule,\n"
                + "ss.schedule_status,\n"
                + "ss.subject_code,\n"
                + "s.subject_id,\n"
                + "s.subject_description,\n"
                + "s.subject_status,\n"
                + "s.subject_lec_units,\n"
                + "s.subject_lab_units,\n"
                + "s.subject_type,\n"
                + "i.instructor_id,\n"
                + "i.instructor_first_name,\n"
                + "i.instructor_middle_name,\n"
                + "i.instructor_last_name\n"
                + "FROM\n"
                + "subject_offerings AS so\n"
                + "INNER JOIN subject_schedules AS ss ON ss.subject_offering_id = so.subject_offering_id\n"
                + "INNER JOIN subjects AS s ON s.subject_id = ss.subject_id\n"
                + "INNER JOIN instructors AS i ON i.instructor_id = ss.instructor_id\n"
                + "INNER JOIN subject_students AS sst ON sst.subject_schedule_id = ss.subject_schedule_id\n"
                + "LEFT JOIN (SELECT * FROM subject_evaluations WHERE student_id=?) se ON ss.subject_schedule_id = se.subject_schedule_id\n"
                + "WHERE i.instructor_visible=1 \n"
                + "AND ss.schedule_status=1\n"
                + "AND sst.student_id=?\n"
                + "AND se.subject_schedule_id is null";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, studentID);
        preparedStatement.setInt(2, studentID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        SubjectSchedules subjectSchedule;
        while (rs.next()) {
            subjectSchedule = new SubjectSchedules();
            subjectSchedule.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            subjectSchedule.setSubjectScheduleID(rs.getInt("subject_schedule_id"));
            subjectSchedule.setScheduleStatus(rs.getInt("schedule_status"));
            subjectSchedule.setSubjectID(rs.getInt("subject_id"));
            subjectSchedule.setSubjectType(rs.getInt("subject_type"));
            subjectSchedule.setSubjectStatus(rs.getInt("subject_status"));
            subjectSchedule.setInstructorID(rs.getInt("instructor_id"));
            subjectSchedule.setSubjectLecUnits(rs.getDouble("subject_lec_units"));
            subjectSchedule.setSubjectLabUnits(rs.getDouble("subject_lab_units"));
            subjectSchedule.setSubjectOfferingName(rs.getString("subject_offering_name"));
            subjectSchedule.setSubjectSchedule(rs.getString("subject_schedule"));
            subjectSchedule.setSubjectCode(rs.getString("subject_code"));
            subjectSchedule.setSubjectDescription(rs.getString("subject_description"));
            subjectSchedule.setInstructorFirstName(rs.getString("instructor_first_name"));
            subjectSchedule.setInstructorMiddleName(rs.getString("instructor_middle_name"));
            subjectSchedule.setInstructorLastName(rs.getString("instructor_last_name"));
            subjectList.add(subjectSchedule);
        }
        closeConnection(getConnectionObject());
        return subjectList;
    }

    /**
     * This method will check if the student evaluated the subject already
     *
     * @param subjectScheduleID - Subject Schedule ID
     * @param studentID - Student ID
     * @return number of duplicate
     */
    public static int checkDuplicate(int subjectScheduleID, int studentID) {
        int duplicate = 0;
        PreparedStatement preparedStatementID;
        try {
            String sql = "SELECT count(1) FROM subject_evaluations WHERE subject_schedule_id=? AND student_id=?";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            preparedStatementID.setInt(1, subjectScheduleID);
            preparedStatementID.setInt(2, studentID);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();
            while (rs.next()) {
                duplicate = rs.getInt(1);
            }

            log.info("duplicate : " + duplicate);
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return duplicate;
    }

    /**
     * This method will check if the student evaluated the subject and specific
     * question already
     *
     * @param subjectScheduleID - Subject Schedule ID
     * @param studentID - Student ID
     * @return number of duplicate
     */
    /**
     * This method will check if the student evaluated the subject and specific
     * question already
     *
     * @param subjectScheduleID - Subject Schedule ID
     * @param studentID - Student ID
     * @param questionID - Question ID
     * @return number of duplicate
     */
    public static int checkDuplicate(int subjectScheduleID, int studentID, int questionID) {
        int duplicate = 0;
        PreparedStatement preparedStatementID;
        try {
            String sql = "SELECT count(1) FROM subject_evaluations WHERE subject_schedule_id=? AND student_id=? AND question_id=?";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            preparedStatementID.setInt(1, subjectScheduleID);
            preparedStatementID.setInt(2, studentID);
            preparedStatementID.setInt(3, questionID);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();
            while (rs.next()) {
                duplicate = rs.getInt(1);
            }

            log.info("duplicate : " + duplicate);
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return duplicate;
    }

    /**
     * Save the evaluation details.If there's an existing record for that
     * subject schedule id and student id it will be deleted
     *
     * @param subjectScheduleID
     * @param studentID
     * @param questionID
     * @param choiceID
     * @return 1 for successful insert 0 for errors
     */
    public static int saveEvaluation(int subjectScheduleID, int studentID, int questionID, int choiceID) throws Exception {
        int result = 0;
        try {
            if (checkDuplicate(subjectScheduleID, studentID, questionID) > 0) {
                deleteEvaluation(subjectScheduleID, studentID);
                throw new Exception("Saving duplicate Question ID is not allowed. Please check the form.");
            }
            if (validateQuestionChoiceID(questionID, choiceID) <= 0) {
                deleteEvaluation(subjectScheduleID, studentID);
                throw new Exception("Invalid choice ID. Please check the form.");
            }
            String sql = "INSERT INTO subject_evaluations(subject_schedule_id, student_id,question_id,choice_id) values(?,?,?,?)";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectScheduleID);
            preparedStatement.setInt(2, studentID);
            preparedStatement.setInt(3, questionID);
            preparedStatement.setInt(4, choiceID);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
        }
        return result;
    }

    /**
     * Delete all row that with
     *
     * @param subjectScheduleID and
     * @param studentID
     * @param subjectScheduleID
     * @param studentID
     * @return
     */
    public static int deleteEvaluation(int subjectScheduleID, int studentID) {
        int result = 0;
        try {
            String sql = "DELETE FROM subject_evaluations WHERE subject_schedule_id=? AND student_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectScheduleID);
            preparedStatement.setInt(2, studentID);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            log.error(ex);
        }
        return result;
    }

    public static int saveEvaluationSuggestion(int subjectScheduleID, int studentID, String suggestion) throws Exception {
        int result = 0;
        try {
            String sql = "INSERT INTO subject_evaluation_suggestions(subject_schedule_id, student_id,subject_evaluation_suggestion) values(?,?,?)";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectScheduleID);
            preparedStatement.setInt(2, studentID);
            preparedStatement.setString(3, suggestion);
            log.info(preparedStatement.toString());
            result = preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
        }
        log.info(result);
        return result;
    }

    public static int deleteEvaluationSuggestion(int subjectScheduleID, int studentID) {
        int result = 0;
        try {
            String sql = "DELETE FROM subject_evaluation_suggestions WHERE subject_schedule_id=? AND student_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectScheduleID);
            preparedStatement.setInt(2, studentID);
            log.info(preparedStatement.toString());
            result = preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
        }
        log.info(result);
        return result;
    }

    public static int checkDuplicateSuggestion(int subjectScheduleID, int studentID) {
        int duplicate = 0;
        PreparedStatement preparedStatementID;
        try {
            String sql = "SELECT count(1) FROM subject_evaluation_suggestions WHERE subject_schedule_id=? AND student_id=?";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            preparedStatementID.setInt(1, subjectScheduleID);
            preparedStatementID.setInt(2, studentID);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();
            while (rs.next()) {
                duplicate = rs.getInt(1);
            }

            log.info("duplicate : " + duplicate);
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return duplicate;
    }

    public static int validateQuestionChoiceID(int questionID, int choiceID) {
        int duplicate = 0;
        PreparedStatement preparedStatementID;
        try {
            String sql = "SELECT count(1) FROM questions_choices WHERE question_id=? AND choice_id=?";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            preparedStatementID.setInt(1, questionID);
            preparedStatementID.setInt(2, choiceID);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();
            while (rs.next()) {
                duplicate = rs.getInt(1);
            }

            log.info("duplicate : " + duplicate);
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return duplicate;
    }
}
